package ru.arriah.servicedesk.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import javax.ejb.EJBException;
import javax.ejb.SessionContext;
import javax.sql.DataSource;

import ru.arriah.common.ejb.GenericSessionBean;
import ru.arriah.servicedesk.bean.DepartmentBean;
import ru.arriah.servicedesk.bean.OrganizationBean;
import ru.arriah.servicedesk.help.DbUtils;
import ru.arriah.servicedesk.help.Utils;

public class OrganizationManager extends GenericSessionBean {
   protected DataSource dataSource;

   enum OrganizationOrderByEnum {
     N,
     name,  
     email 
   };
   
   enum DepartmentOrderByEnum {
     N,
     name
   };
   
   public OrganizationManager() {
      super();
      dataSource = null;
   }
   
   

   public void setSessionContext(
      SessionContext sessionContext
   ) throws EJBException, RemoteException {
      super.setSessionContext(sessionContext);

      try {
         this.dataSource = Utils.getDataSource(); 
      } catch (Exception e) {
         handleException(e, "setSessionContext");
         throw new EJBException(e);
      }
   }
   
   
   
   public Collection<OrganizationBean> selectOrganizations() {
      return this.selectOrganizations("N", "ASC", "'active'");
   }

   
   
   public Collection<OrganizationBean> selectOrganizations(
      String orderBy,
      String order, 
      String status
   ) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;

      try {
         ArrayList<OrganizationBean> organizationsList = 
            new ArrayList<OrganizationBean>();

         order = (order.equals("ASC") ? "ASC" : "DESC");

         try {
            OrganizationOrderByEnum orderEnum = 
               OrganizationOrderByEnum.valueOf(orderBy);

            switch (orderEnum) {
            case N:
               orderBy = "`organization`.`id`";
               break;
            case name:
               orderBy = "`organization`.`shorttitle`";
               break;
            case email:
               orderBy = "`organization`.`email1`";
               break;
            default:
               orderBy = "`organization`.`id`";
               break;
            }
         } catch (Exception e) {
            orderBy = "`organization`.`id`";
         }

         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `organization`" + 
            " WHERE `status` IN (" + status + ")" + 
            " ORDER BY " + orderBy + " " + order
         );

         resultSet = preparedStatement.executeQuery();

         while (resultSet.next()) {
            OrganizationBean organizationBean = new OrganizationBean();
            organizationBean.setId(resultSet.getInt("id"));
            organizationBean.setShortTitle(resultSet.getString("shorttitle"));
            organizationBean.setFullTitle(resultSet.getString("fulltitle"));
            organizationBean.setPostalAddress(resultSet.getString("postaladdress"));
            organizationBean.setKpp(resultSet.getString("kpp"));
            organizationBean.setInn(resultSet.getString("inn"));
            organizationBean.setSettlementAccount(resultSet.getString("settlementaccount"));
            organizationBean.setBankTitle(resultSet.getString("banktitle"));
            organizationBean.setCorrAccount(resultSet.getString("corraccount"));
            organizationBean.setBik(resultSet.getString("bik"));
            organizationBean.setTel1(resultSet.getString("tel1"));
            organizationBean.setTel2(resultSet.getString("tel2"));
            organizationBean.setFax(resultSet.getString("fax"));
            organizationBean.setEmail1(resultSet.getString("email1"));
            organizationBean.setEmail2(resultSet.getString("email2"));
            organizationBean.setWww(resultSet.getString("www"));
            organizationBean.setNotes(resultSet.getString("notes"));
            organizationBean.setBusinessType(resultSet.getString("businesstype"));
            organizationBean.setStatus(resultSet.getString("status"));

            organizationsList.add(organizationBean);
         }

         return organizationsList;
      } catch (Exception e) {
         handleException(e, "selectOrganizations");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
   
   
   
   public OrganizationBean selectOrganization(int organizationId) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;

      try {
         OrganizationBean organizationBean = new OrganizationBean();

         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `organization`" + 
            " WHERE `organization`.`id` = ?" + 
            " AND `status` = 'active'"
         );
         
         preparedStatement.setInt(1, organizationId);

         resultSet = preparedStatement.executeQuery();

         if (resultSet.next()) {
            organizationBean.setId(resultSet.getInt("id"));
            organizationBean.setShortTitle(resultSet.getString("shorttitle"));
            organizationBean.setFullTitle(resultSet.getString("fulltitle"));
            organizationBean.setPostalAddress(resultSet.getString("postaladdress"));
            organizationBean.setKpp(resultSet.getString("kpp"));
            organizationBean.setInn(resultSet.getString("inn"));
            organizationBean.setSettlementAccount(resultSet.getString("settlementaccount"));
            organizationBean.setBankTitle(resultSet.getString("banktitle"));
            organizationBean.setCorrAccount(resultSet.getString("corraccount"));
            organizationBean.setBik(resultSet.getString("bik"));
            organizationBean.setTel1(resultSet.getString("tel1"));
            organizationBean.setTel2(resultSet.getString("tel2"));
            organizationBean.setFax(resultSet.getString("fax"));
            organizationBean.setEmail1(resultSet.getString("email1"));
            organizationBean.setEmail2(resultSet.getString("email2"));
            organizationBean.setWww(resultSet.getString("www"));
            organizationBean.setNotes(resultSet.getString("notes"));
            organizationBean.setBusinessType(resultSet.getString("businesstype"));
         }

         return organizationBean;
      } catch (Exception e) {
         handleException(e, "selectOrganization");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }
   
   
   
   public void addOrganization(OrganizationBean organizationBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {                    
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "INSERT INTO `organization`" +
           " SET `shorttitle` = ?," +
           " `fulltitle` = ?," +
           " `postaladdress` = ?," +
           " `kpp` = ?," +
           " `inn` = ?," +
           " `settlementaccount` = ?," +
           " `banktitle` = ?," +
           " `corraccount` = ?," +
           " `bik` = ?," +
           " `tel1` = ?," +
           " `tel2` = ?," +
           " `fax` = ?," +
           " `email1` = ?," +
           " `email2` = ?," +
           " `www` = ?," +
           " `notes` = ?," +
           " `businesstype` = ?"           
       );                     
                      
       preparedStatement.setString(1, organizationBean.getShortTitle());
       preparedStatement.setString(2, organizationBean.getFullTitle());
       preparedStatement.setString(3, organizationBean.getPostalAddress());
       preparedStatement.setString(4, organizationBean.getKpp());
       preparedStatement.setString(5, organizationBean.getInn());
       preparedStatement.setString(6, organizationBean.getSettlementAccount());         
       preparedStatement.setString(7, organizationBean.getBankTitle());         
       preparedStatement.setString(8, organizationBean.getCorrAccount());
       preparedStatement.setString(9, organizationBean.getBik());
       preparedStatement.setString(10, organizationBean.getTel1());
       preparedStatement.setString(11, organizationBean.getTel2());
       preparedStatement.setString(12, organizationBean.getFax());
       preparedStatement.setString(13, organizationBean.getEmail1());
       preparedStatement.setString(14, organizationBean.getEmail2());
       preparedStatement.setString(15, organizationBean.getWww());
       preparedStatement.setString(16, organizationBean.getNotes());        
       preparedStatement.setString(17, organizationBean.getBusinessType());       
       
       preparedStatement.executeUpdate();
              
           
     } catch (Exception e){
        handleException(e, "addOrganization");
        throw new EJBException(e);         
     }  finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }  
   }
   
   
   
   public void updateOrganization(OrganizationBean organizationBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {                    
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `organization`" +
           " SET `shorttitle` = ?," +
           " `fulltitle` = ?," +
           " `postaladdress` = ?," +
           " `kpp` = ?," +
           " `inn` = ?," +
           " `settlementaccount` = ?," +
           " `banktitle` = ?," +
           " `corraccount` = ?," +
           " `bik` = ?," +
           " `tel1` = ?," +
           " `tel2` = ?," +
           " `fax` = ?," +
           " `email1` = ?," +
           " `email2` = ?," +
           " `www` = ?," +
           " `notes` = ?," +
           " `businesstype` = ?" +
           " WHERE `id` = ?"
       );                    
                      
       preparedStatement.setString(1, organizationBean.getShortTitle());
       preparedStatement.setString(2, organizationBean.getFullTitle());
       preparedStatement.setString(3, organizationBean.getPostalAddress());
       preparedStatement.setString(4, organizationBean.getKpp());
       preparedStatement.setString(5, organizationBean.getInn());
       preparedStatement.setString(6, organizationBean.getSettlementAccount());         
       preparedStatement.setString(7, organizationBean.getBankTitle());         
       preparedStatement.setString(8, organizationBean.getCorrAccount());
       preparedStatement.setString(9, organizationBean.getBik());
       preparedStatement.setString(10, organizationBean.getTel1());
       preparedStatement.setString(11, organizationBean.getTel2());
       preparedStatement.setString(12, organizationBean.getFax());
       preparedStatement.setString(13, organizationBean.getEmail1());
       preparedStatement.setString(14, organizationBean.getEmail2());
       preparedStatement.setString(15, organizationBean.getWww());
       preparedStatement.setString(16, organizationBean.getNotes());        
       preparedStatement.setString(17, organizationBean.getBusinessType());       
       preparedStatement.setInt(18, organizationBean.getId());
       
       preparedStatement.executeUpdate();
                     
     } catch (Exception e){
        handleException(e, "updateOrganization");
        throw new EJBException(e);         
     }  finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }
   }
   
   
   
   public void deleteOrganization(int organizationId) {
      this.setOrganizationStatus(organizationId, "inactive");
   }
   
   
   
   public void restoreOrganization(int organizationId) {
      this.setOrganizationStatus(organizationId, "active");
   }
   
   
   
   private void setOrganizationStatus(int organizationId, String status) {
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      
      try {              
        connection = dataSource.getConnection();       
        preparedStatement = connection.prepareStatement(
            "UPDATE `organization`" +
            " SET `status` = ?" +
            " WHERE `id` = ?"
        );                  
        
        preparedStatement.setString(1, status);
        preparedStatement.setInt(2, organizationId); 
        preparedStatement.executeUpdate();
                           
      } catch (Exception e){
         handleException(e, "setOrganizationStatus");
         throw new EJBException(e);         
      }  finally {
         DbUtils.closeQuietly(connection, preparedStatement);
      }   
   }
   
   
   
   private void setDepartmentStaus(int departmentId, String status) {
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      try {              
        connection = dataSource.getConnection();       
        preparedStatement = connection.prepareStatement(
            "UPDATE `department`" +
            " SET `status` = 'inactive'" +
            " WHERE `id` = ?"
        );                  
        
        preparedStatement.setString(1, status);
        preparedStatement.setInt(2, departmentId); 
        preparedStatement.executeUpdate();              
              
      } catch (Exception e){
         handleException(e, "setDepartmentStaus");
         throw new EJBException(e);         
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement);
      }
    }
   
   
   
   public void deleteDepartment(int departmentId) {
     this.setDepartmentStaus(departmentId, "inactive");
   }
   
   
   
   public void restoreDepartment(int departmentId) {
      this.setDepartmentStaus(departmentId, "active");
   }
   
   
   
   public void addDepartment(DepartmentBean departmentBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {                    
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "INSERT INTO `department`" +
           " SET `name` = ?," +
           " `organization_id` = ?"          
       );                     
                      
       preparedStatement.setString(1, departmentBean.getName());
       preparedStatement.setInt(2, departmentBean.getOrganizationId());              
       
       preparedStatement.executeUpdate();
                          
     } catch (Exception e){
        handleException(e, "addDepartment");
        throw new EJBException(e);         
     }  finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }
   }
   
   
   
   public void updateDepartment(DepartmentBean departmentBean) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     try {                    
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "UPDATE `department`" +
           " SET `name` = ?" +
           " WHERE `department`.`id` = ?"          
       );                     
                      
       preparedStatement.setString(1, departmentBean.getName());
       preparedStatement.setInt(2, departmentBean.getId());              
       
       preparedStatement.executeUpdate();
                         
     } catch (Exception e){
        handleException(e, "updateDepartment");
        throw new EJBException(e);         
     }  finally {
        DbUtils.closeQuietly(connection, preparedStatement);
     }
   }
   
   
   
   public Collection<DepartmentBean> selectDepartments(int organizationId) {
     return this.selectDepartments(organizationId, "N", "ASC", "'active'");
   }
   
   
   
   public Collection<DepartmentBean> selectDepartments(
      int organizationId,
      String orderBy, String order, String status
   ) {
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {
         ArrayList<DepartmentBean> departmentsList = new ArrayList<DepartmentBean>();

         order = (order.equals("ASC") ? "ASC" : "DESC");

         try {
            DepartmentOrderByEnum orderEnum = DepartmentOrderByEnum.valueOf(orderBy);

            switch (orderEnum) {
            case N:
               orderBy = "`department`.`id`";
               break;
            case name:
               orderBy = "`department`.`name`";
               break;
            default:
               orderBy = "`department`.`id`";
               break;
            }
         } catch (Exception e) {
            orderBy = "`department`.`id`";
         }

         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `department`" + 
            " WHERE `status` IN (" + status + ")" + 
            " AND `organization_id` = ?" + 
            " ORDER BY " + orderBy + " " + order
         );

         preparedStatement.setInt(1, organizationId);

         resultSet = preparedStatement.executeQuery();

         while (resultSet.next()) {
            DepartmentBean departmentBean = new DepartmentBean();
            departmentBean.setId(resultSet.getInt("id"));
            departmentBean.setName(resultSet.getString("name"));
            departmentBean.setStatus(resultSet.getString("status"));
            departmentsList.add(departmentBean);
         }

         return departmentsList;
      } catch (Exception e) {
         handleException(e, "selectDepartments");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }
   
   
   
   public DepartmentBean selectDepartment(int departmentId) {
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;

      try {
         DepartmentBean departmentBean = new DepartmentBean();

         connection = dataSource.getConnection();
         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `department`" + 
            " WHERE `department`.`id` = ?"
         );

         preparedStatement.setInt(1, departmentId);

         resultSet = preparedStatement.executeQuery();

         if (resultSet.next()) {
            departmentBean.setId(resultSet.getInt("id"));
            departmentBean.setName(resultSet.getString("name"));
            departmentBean.setStatus(resultSet.getString("status"));
         }

         return departmentBean;
      } catch (Exception e) {
         handleException(e, "selectDepartment");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }

}